#!/usr/bin/env python
# -*- coding: utf-8 -*-

# Volturnus Address Book svn

# -----------------------------------------------------------------------
# Copyright (C) 2007 Bryan Smith

# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.

# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
# -----------------------------------------------------------------------

import sqlite3
import sys

## Vol Class
#
#  Main class. Interaction with the database is handled by this class.
class Vol:

    ## Constructor
    def __init__(self):
        try:
            print "== Volturnus Address Book =="
            self.v_connection = sqlite3.connect('volturnus.db') # Connect to the db
            self.v_cursor = self.v_connection.cursor() # initialize the database and create the cursor
        except StandardError:
            print "Database open error. Check to see that the database exists and that you have read permission."

    ## Sort ABC method
    #
    #  Sorts the entries in the database.
    #  @param self
    def sort_abc(self):
        sort_info = "SELECT * FROM volturnus ORDER BY lname" # Select each name and order by last name (lname)
        self.v_cursor.execute(sort_info)
        self.v_connection.commit() # commit the ordered list as the new order
        
    ## Options method
    #
    #  Presents the options to the user (such as add name and delete name).
    #  @param self
    def options(self):
        print """
        --------- Options ---------
        | a) Add Name             |
        | b) Delete Name          |
        | c) Edit Name            |
        | d) View All Names       |
        | e) Lookup by Last Name  |
        | f) Lookup by City/Town  |
        | g) Export to HTML       | 
        | h) Export to CSV        |
        | i) About                |
        | j) Exit                 |
        ---------------------------
        """
        opt = raw_input(":> ").lower()
        if opt == 'a':
            self.add_name() # Add name
        elif opt == 'b':
            self.delete_name() # Delete name
        elif opt == 'c':
            self.edit_name() # Edit name
        elif opt == 'd':
            self.view_all_names() # View names
        elif opt == 'e':
            self.lookup_last_name() # Lookup by last name
        elif opt == 'f':
            self.lookup_city_town() # Lookup by town/city
        elif opt == 'g':
            self.export_to_html() # Export info to html doc
        elif opt == 'h':
            self.export_to_csv() # Export to CSV (Comma Sepearated Values)
        elif opt == 'i':
            self.about() # Print about info
        elif opt == 'j':
            self.v_connection.close() # close the database
            sys.exit # exit
        else:
            print "Invalid choice."
            self.options()

    ## Add Name method
    #
    #  Adds a name to the database.
    #  @param self
    def add_name(self):
        fname = raw_input("First Name:> ")
        lname = raw_input("Last Name:> ")
        street = raw_input("Street:> ")
        town = raw_input("City/Town:> ")
        province = raw_input("Province/State:> ")
        postal = raw_input("Postal/Zip Code:> ")
        country = raw_input("Country:> ")
        home_phone_number = raw_input("Home Phone Number:> ")
        work_phone_number = raw_input("Work Phone Number:> ")
        try:
            response = "INSERT INTO volturnus VALUES('" + lname + "','" + fname + "','" + street + \
                           "','" + town + "','" + province + "','" + postal + \
                           "','" + country + "','" + home_phone_number + "','" + work_phone_number + "')"
                # insert the info into the database using an sql query
            self.v_cursor.execute(response)
            self.v_connection.commit() # commit the contact info to the database
        except StandardError:
            print "Insertion failed. Check write permissions."
        self.options()

    ## Delete Name method
    #
    #  Deletes names from the database.
    #  @param self
    def delete_name(self):
        del_fname = raw_input("First Name:> ")
        del_lname = raw_input("Last Name:> ")
        del_info = "SELECT * from volturnus"
        self.v_cursor.execute(del_info)
        full_name = del_fname + del_lname # add the two names together
        for info in self.v_cursor.fetchall():
            del_name = info[1] + info[0] # add the two names together (for comparison to full_name)
            if full_name.lower() == del_name.lower(): # Make both lower case for better comparison (ignoring case)
                # Print the name to show who should be deleted (in case it was a mistake)
                print "\nFirst Name: %s" % info[0]
                print "Last Name: %s" % info[1]
                print "Street: %s" % info[2]
                print "City/Town: %s" % info[3]
                print "Province/State: %s" % info[4]
                print "Postal/Zip Code: %s" % info[5]
                print "Country: %s" % info[6]
                print "Home Phone Number: %s" % info[7]
                print "Work Phone Number: %s" % info[8] 
                sure = raw_input("\nAre you sure you want to delete this person (y/n)? ") # ask for confirmation from the user
                if sure.lower() == 'y':
                    try:
                        self.v_cursor.execute("DELETE FROM volturnus WHERE lname = '" + info[0] + "'" + "AND fname ='" + info[1] + "'")
                        self.v_connection.commit() # delete the contact and commit the changes to the sqlite database
                    except StandardError:
                        print "Insertion failed. Check write permissions."
                    print "Contact deleted."
                elif sure.lower() == 'n': # In case the individual changes their mind or selects wrong individual
                    print "Contact retained."
                else:
                    print "Invalid option."
        self.options()

    ## Edit Name method
    #
    #  Edits names in the database.
    #  @param self
    def edit_name(self):
        edit_fname = raw_input("First Name:> ")
        edit_lname = raw_input("Last Name:> ")
        edit_info = "SELECT * from volturnus"
        self.v_cursor.execute(edit_info)
        full_name = edit_fname + edit_lname # add the two names together
        for info in self.v_cursor.fetchall():
            edit_name = info[1] + info[0] # add the two names together (for comparison to full_name)
            if full_name.lower() == edit_name.lower():
                print "What piece of information would you like to change for " + info[1] + " " + info[0] + "?"
                print """
        a) First Name
        b) Last Name
        c) Street
        d) City/Town
        e) Province/State
        f) Postal/Zip Code
        g) Country
        h) Home Phone Number
        i) Work Phone Number
                """ # ask the user which piece of info they would like to change
                edit_opt = raw_input(":> ")
                # update each element according to the user input
                if edit_opt == 'a':
                    new_name = raw_input("New First Name :> ")
                    self.v_cursor.execute("UPDATE volturnus SET fname = '" + new_name + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                elif edit_opt == 'b':
                    new_name = raw_input("New Last Name :> ")
                    self.v_cursor.execute("UPDATE volturnus SET lname = '" + new_name + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                elif edit_opt == 'c':
                    new_street = raw_input("New Street :> ")
                    self.v_cursor.execute("UPDATE volturnus SET street = '" + new_street + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                elif edit_opt == 'd':
                    new_town = raw_input("New City/Town :> ")
                    self.v_cursor.execute("UPDATE volturnus SET town = '" + new_town + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                elif edit_opt == 'e':
                    new_province = raw_input("New Province/State :> ")
                    self.v_cursor.execute("UPDATE volturnus SET province = '" + new_province + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                elif edit_opt == 'f':
                    new_postal = raw_input("New Postal/ZIP Code :> ")
                    self.v_cursor.execute("UPDATE volturnus SET postalcode = '" + new_postal + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                elif edit_opt == 'g':
                    new_country = raw_input("New Country :> ")
                    self.v_cursor.execute("UPDATE volturnus SET country = '" + new_country + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                elif edit_opt == 'h':
                    new_home_phone_number = raw_input("New Home Phone Number :> ")
                    self.v_cursor.execute("UPDATE volturnus SET homephone = '" + new_home_phone_number + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                elif edit_opt == 'i':
                    new_work_phone_number = raw_input("New Home Phone Number :> ")
                    self.v_cursor.execute("UPDATE volturnus SET workphone = '" + new_work_phone_number + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                else:
                    print "Invalid option."
                self.v_connection.commit() # commit the new information
        self.options()

    ## View All Names method
    #
    #  View all the names in the database.
    #  @param self
    def view_all_names(self):
        try:
            get_info = "SELECT * from volturnus"
            self.v_cursor.execute(get_info)
            self.sort_abc() # sort the users before printing them
        except StandardError:
            print "Retrieval failed. Check read permissions."
        for info in self.v_cursor.fetchall():
            print "\nFirst Name: %s" % info[1]
            print "Last Name: %s" % info[0]
            print "Street: %s" % info[2]
            print "City/Town: %s" % info[3]
            print "Province/State: %s" % info[4]
            print "Postal/Zip Code: %s" % info[5]
            print "Country: %s" % info[6]
            print "Home Phone Number: %s" % info[7]
            print "Work Phone Number: %s" % info[8]
        self.options()

    ## Lookup By Last Name method
    #
    #  Lookup individual by last name.
    #  @param self
    def lookup_last_name(self):
        lookup_name = raw_input("Last Name :> ")
        try:
            lookup_info = "SELECT * from volturnus"
            self.v_cursor.execute(lookup_info)
        except StandardError:
            print "Retrieval failed. Check read permissions."
        for info in self.v_cursor.fetchall():
            if info[0].lower() == lookup_name.lower(): # if the last name equals inputed name, print the info
                print "\nFirst Name: %s" % info[1]
                print "Last Name: %s" % info[0]
                print "Street: %s" % info[2]
                print "City/Town: %s" % info[3]
                print "Province/State: %s" % info[4]
                print "Postal/Zip Code: %s" % info[5]
                print "Country: %s" % info[6]
                print "Home Phone Number: %s" % info[7]
                print "Work Phone Number: %s" % info[8]
        self.options()

    ## Lookup By City/Town method
    #
    #  Lookup individual by city/town.
    #  @param self
    def lookup_city_town(self):
        lookup_town = raw_input("City/Town :> ")
        try:
            lookup_info = "SELECT * from volturnus"
            self.v_cursor.execute(lookup_info)
        except StandardError:
            print "Retrieval failed. Check read permissions."
        for info in self.v_cursor.fetchall():
            if info[3].lower() == lookup_town.lower(): # if the town equals inputed town, print the info
                print "\nFirst Name: %s" % info[1]
                print "Last Name: %s" % info[0]
                print "Street: %s" % info[2]
                print "City/Town: %s" % info[3]
                print "Province/State: %s" % info[4]
                print "Postal/Zip Code: %s" % info[5]
                print "Country: %s" % info[6]
                print "Home Phone Number: %s" % info[7]
                print "Work Phone Number: %s" % info[8]
        self.options()

    ## Export to HTML method
    #
    #  Export the contact list to an HTML document.
    #  @param self
    def export_to_html(self):
        # page variable will hold html contents - as it is here, it holds the beginning of the document
        page = """
<html>
<title>Volturnus Contact List</title>
<center>
<h2>Contacts</h2>
<hr>
<table border=1>
<tr bgcolor=#cccccc>
<td>Last Name</td>
<td>First Name</td>
<td>Street</td>
<td>City/Town</td>
<td>Province/State</td>
<td>Postal/Zip Code</td>
<td>Country</td>
<td>Home Phone Number</td>
<td>Work Phone Number</td>
</tr>
""" # this will hold the contents of the page
        print "Collecting data..."
        try:
            page_info = "SELECT * from volturnus"
            self.v_cursor.execute(page_info)
            self.sort_abc() # Sort the names before outputting them to a table
        except StandardError:
            print "Retrieval failed. Check read permissions."
        for info in self.v_cursor.fetchall():
            page += "<tr>\n<td>" + info[0] + "</td>\n<td>" + info[1] + "</td>\n<td>" + info[2] +  \
                    "</td>\n<td>" + info[3] +"</td>\n<td>" + info[4] + "</td>\n<td>" + info[5] + \
                    "</td>\n<td>" + info[6] + "</td>\n<td>" + info[7] + "</td>\n<td>" + info[8] +"</td>\n</tr>\n" # add the data to a row on the table
        page+= """</table>
<hr>
<font size=2>Table constructed using <a href="http://code.google.com/p/volturnus">Volturnus Address Book</a>.
</font>
</center>
</html>
""" # finish off the page
        try:
            v_output = open('Volturnus Contacts.html', 'w')
            v_output.write(page) # write the string (page) with the html to contact_output.html for viewing outside of volturnus
            v_output.close()
            print "Contacts written to 'Volturnus Contacts.html'"
        except IOError:
            print "Can not write to file. Check permissions."
        self.options()

    ## Export to CSV method
    #
    #  Export the contact list to a comma seperated values document.
    #  @param self
    def export_to_csv(self):
        csv_contents = ''
        print "Collecting data..."
        try:
            page_info = "SELECT * from volturnus"
            self.v_cursor.execute(page_info)
            self.sort_abc() # Sort the names before outputting them to csv
        except StandardError:
            print "Retrieval failed. Check read permissions."
        for info in self.v_cursor.fetchall():
            csv_contents += "\n" + info[1] + "," + info[0] + "," + info[1] + " " + info[0] + ",,,," + info[8] \
                             + "," + info[7] + ",,,," + info[2] + "," + info[3] + "," + info[4] + "," + info[5] \
                             + "," + info[6] + ",,,,,,,,,,,,,,,,,,,"
        try:
            v_output = open('Volturnus Contacts.csv', 'w')
            v_output.write(csv_contents) # write the string (page) with the csv to Volturnus Contacts.csv for use in alternate address books
            v_output.close()
            print "Contacts written to 'Volturnus Contacts.csv'"
        except IOError:
            print "Can not write to file. Check permissions."
        self.options()

    ## About method
    #
    #  About Volturnus.
    #  @param self
    def about(self):
        print "\n\n== Volturnus Address Book =="
        print "Written by Bryan Smith."
        print "Copyright 2007 Bryan Smith."
        print "Version 0.2"
        print "Licensed under the GPL v.2 (GNU General Public License version 2)"
        print "To view the license, read the included LICENSE file"
        self.options()

if __name__ == '__main__':
    v = Vol()
    v.options()
